/*
 * Copyright © 2004-2014 chenYuan. All rights reserved.
 * @Website:wwww.jspx.net
 * @Mail:39793751@qq.com
 * @author: chenYuan , 陈原
 * @License: Jspx.net Framework Code is open source (LGPL)，Jspx.net Framework 使用LGPL 开源授权协议发布。
 * @jvm:jdk1.6+  x86/amd64
 *
 */
package com.jspx.sober.jdbc;

import com.jspx.boot.EnvFactory;
import com.jspx.boot.environment.Placeholder;
import com.jspx.cache.JSCacheManager;
import com.jspx.sober.*;
import com.jspx.sober.transaction.AbstractTransaction;
import com.jspx.sober.config.*;
import com.jspx.sober.criteria.expression.Expression;
import com.jspx.sober.criteria.projection.Projections;
import com.jspx.sober.dialect.Dialect;
import com.jspx.sober.exception.ValidException;
import com.jspx.sober.impl.CriteriaImpl;
import com.jspx.sober.impl.SqlMapClientImpl;
import com.jspx.sober.sequences.SequencesDAO;
import com.jspx.sober.sequences.impl.SequencesDAOImpl;
import com.jspx.sober.ssql.SSqlExpression;
import com.jspx.sober.util.AnnotationUtil;
import com.jspx.sober.util.DataMap;
import com.jspx.sober.util.JdbcUtil;
import com.jspx.sober.util.SoberUtil;
import com.jspx.utils.*;
import com.jspx.scriptmark.ScriptRunner;
import com.jspx.json.JSONException;
import com.jspx.json.JSONArray;
import com.jspx.json.JSONObject;
import com.jspx.scriptmark.core.script.TemplateScriptEngine;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.naming.NamingException;
import java.io.InputStream;
import java.io.Serializable;
import java.io.IOException;
import java.math.BigDecimal;
import java.sql.*;
import java.util.*;

/**
 * Created by IntelliJ IDEA.
 * User:chenYuan (mail:39793751@qq.com)
 * Date: 2007-1-6
 * Time: 15:19:38
 * JDBC 数据库操作
 */
public abstract class JdbcOperations implements SoberSupport {

    protected static final Logger log = LoggerFactory.getLogger(JdbcOperations.class);
    private static SequencesDAO sequencesDAO;
    private Dialect dialect = null;
    private SoberFactory soberFactory;
    private int tid = SoberEnv.notTransaction;

    public JdbcOperations() {

    }

    public int getTid() {
        return tid;
    }

    public void setTid(int tid) {
        this.tid = tid;
    }

    public int getMaxRows() {
        return this.soberFactory.getMaxRows();
    }

    public SoberFactory getSoberFactory() {
        return soberFactory;
    }

    public void setSoberFactory(SoberFactory soberFactory) {
        this.soberFactory = soberFactory;
        dialect = this.soberFactory.getDialect();
    }

    public TableModels getSoberTable(Class cla) {

        return soberFactory.getTableModels(cla, this);
    }

    /**
     * @param cla   类对象
     * @param field 字段名称
     * @return 判断是否存在此字段
     */
    public boolean containsField(Class cla, String field) {
        if (!StringUtil.hasLength(field) || cla == null) return false;
        TableModels tableModels = getSoberTable(cla);
        return tableModels.containsField(field);
    }

    /**
     * @param cla         类
     * @param notFileds   没有的字段
     * @param editColumns 编辑字段
     * @param hideColumns 隐藏字段
     * @return 得到字段json 格式
     * @throws JSONException
     */
    public String getColumnModels(Class cla, String[] notFileds, String[] editColumns, String[] hideColumns) throws JSONException {
        TableModels soberTable = getSoberTable(cla);
        JSONArray json = new JSONArray();
        String[] fieldArray = soberTable.getFieldArray();
        for (String field : fieldArray) {
            SoberColumn soberColumn = soberTable.getColumn(field);
            if (ArrayUtil.inArray(notFileds, soberColumn.getName(), true)) continue;
            JSONObject jsonFiled = new JSONObject();
            jsonFiled.put("name", soberColumn.getName());
            jsonFiled.put("caption", soberColumn.getCaption());
            jsonFiled.put("type", soberColumn.getType());
            jsonFiled.put("dataType", soberColumn.getDataType());
            jsonFiled.put("option", soberColumn.getOption());
            if (ArrayUtil.inArray(editColumns, soberColumn.getName(), true)) {
                jsonFiled.put("edit", "true");
            } else {
                jsonFiled.put("edit", "false");
            }
            if (ArrayUtil.inArray(hideColumns, soberColumn.getName(), true)) {
                jsonFiled.put("hide", "true");
            } else {
                jsonFiled.put("hide", "false");
            }

            if (soberColumn.getOption() != null && soberColumn.getOption().length() > 2) {
                jsonFiled.put("input", "select");
            } else {
                jsonFiled.put("input", soberColumn.getInput());
            }
            json.put(jsonFiled);
        }
        return json.toString(1);
    }

    /**
     * 只为方便扩展使用
     *
     * @return 返回一个连接
     * @throws Exception
     */
    public Connection getConnection(final int type) throws Exception {
        return soberFactory.getConnection(type, tid);
    }


    /**
     * 只为方便扩展使用
     *
     * @param conn 关闭连接
     */
    public void closeConnection(Connection conn) {
        soberFactory.closeConnection(conn, false);
    }

    /**
     * @return 系列号生成器
     */
    private SequencesDAO getSequencesDAO() {
        if (sequencesDAO == null) {
            sequencesDAO = new SequencesDAOImpl(this);
        }
        return sequencesDAO;
    }

    /**
     * @return 保存提交点, 开始一个事务
     * @throws SQLException    SQL 异常
     * @throws NamingException 事物命名空间异常
     */
    public Transaction createTransaction() throws SQLException {
        AbstractTransaction transaction = soberFactory.createTransaction(this);
        tid = transaction.getTid();
        return transaction;
    }

    /**
     * @param cla       类
     * @param resultSet 查询返回
     * @return 载入映射关系
     * @throws Exception 异常
     */
    public Object loadColumnsValue(Class cla, ResultSet resultSet) throws Exception {
        return loadColumnsValue(getSoberTable(cla), resultSet);
    }

    /**
     * 设置字段数据(无映射关系)
     *
     * @param resultSet  jdbc
     * @param soberTable mapping
     * @return Object 载入数据
     * @throws Exception 异常
     */
    public Object loadColumnsValue(TableModels soberTable, ResultSet resultSet) throws Exception {
        Object result = soberTable.getEntity().newInstance();
        ResultSetMetaData metaData = resultSet.getMetaData();
        int count = metaData.getColumnCount();
        for (int i = 1; i <= count; i++) {
            String dbFiled = metaData.getColumnLabel(i);
            SoberColumn soberColumn = soberTable.getColumn(dbFiled);
            if (soberColumn != null) {
                Object obj = dialect.getResultSetValue(resultSet, i);
                BeanUtil.setSimpleProperty(result, soberColumn.getName(), obj);
            } else if (ClassUtil.isSetMethod(result.getClass(), dbFiled)) {
                BeanUtil.setSimpleProperty(result, dbFiled, dialect.getResultSetValue(resultSet, i));
            }
        }
        return result;
    }

    /**
     * 计算
     * 请假条
     *
     * @param soberTable 结果关系表
     * @param obj        对象
     * @throws Exception 异常
     */
    public void calcUnique(TableModels soberTable, Object obj) throws Exception {
        if (obj == null) return;
        ////////////////////////////CalcUnique
        Map<String, SoberCalcUnique> calcUniqueMap = soberTable.getCalcUniqueMap();
        for (String colName : calcUniqueMap.keySet()) {
            SoberCalcUnique soberCalcUnique = calcUniqueMap.get(colName);
            Map<String, Object> valueMap = new HashMap<String, Object>();
            Class[] classArray = soberCalcUnique.getEntity();
            if (classArray != null) {
                for (Class aClassArray : classArray) {
                    String tableName = StringUtil.uncapitalize(StringUtil.substringAfterLast(aClassArray.getName(), "."));
                    if (!StringUtil.hasLength(tableName)) tableName = aClassArray.getName();
                    TableModels calcTable = getSoberTable(aClassArray);
                    valueMap.put(tableName + "Table", calcTable.getTableName());
                }
            }
            String sqlText = dialect.processSQL(soberCalcUnique.getSql(), valueMap);
            try {
                Object[] param = null;
                if (!ArrayUtil.isEmpty(soberCalcUnique.getValue())) {
                    for (String key : soberCalcUnique.getValue()) {
                        param = ArrayUtil.add(param, BeanUtil.getProperty(obj, key));
                    }
                }
                BeanUtil.setSimpleProperty(obj, colName, getUniqueResult(sqlText, param));
            } catch (Exception e) {
                log.error(soberTable.getTableName() + ":" + sqlText, e);
                e.printStackTrace();
            }
        }
        ////////////////////////////
    }

    /**
     * 载入关联列表
     *
     * @param cla  类
     * @param list 对象实体列表
     * @throws Exception
     */
    public void loadNexusList(Class cla, List list) throws Exception {
        loadNexusList(getSoberTable(cla), list);
    }

    /**
     * 载入映射对象
     *
     * @param soberTable mapping
     * @param list       list
     * @throws Exception
     */
    @SuppressWarnings("unchecked")
    public void loadNexusList(TableModels soberTable, List list) throws Exception {
        for (Object object : list) {
            loadNexusValue(getSoberTable(object.getClass()), object);
        }
    }


    /**
     * @param soberTable 结构
     * @param result     对象
     * @throws Exception
     */
    public void loadNexusValue(TableModels soberTable, Object result) throws Exception {
        if (result == null) return;
        try {
            Map<String, SoberNexus> nexus = soberTable.getNexusMap();
            for (String colName : nexus.keySet()) {
                SoberNexus soberNexus = nexus.get(colName);
                Placeholder placeholder = EnvFactory.getPlaceholder();
                if ((SoberEnv.OneToOne.equalsIgnoreCase(soberNexus.getMapping()) || SoberEnv.ManyToOne.equalsIgnoreCase(soberNexus.getMapping())) && (StringUtil.isNULL(soberNexus.getWhere())
                        || !StringUtil.isNULL(soberNexus.getWhere()) && ObjectUtil.toBoolean(placeholder.processTemplate(BeanUtil.toMap(result), soberNexus.getWhere())))) {

                    Object findValue = BeanUtil.getProperty(result, soberNexus.getName());

                    String term = AnnotationUtil.getNexusTerm(result, soberNexus.getTerm());

                    TableModels targetModels = getSoberTable(soberNexus.getTargetEntity());
                    SoberColumn soberColumn = targetModels.getColumn(soberNexus.getTargetName());

                    if (soberColumn == null) continue;

                    Class classType = soberColumn.getClassType();
                    //id 为 0 的时候不查询
                    if ((classType == Long.class || classType == long.class || classType == Integer.class || classType == int.class) && ObjectUtil.toInt(findValue) == 0) {
                        continue;
                    }
                    List childList = getFindFieldList(soberNexus.getTargetEntity(), soberNexus.getTargetName(), (Serializable) findValue, term,
                            AnnotationUtil.getNexusOrderBy(result, soberNexus.getOrderBy()), false, 1);
                    if (!childList.isEmpty()) {
                        Object chainObj = childList.get(0);
                        if (soberNexus.isChain() && chainObj != null) {
                            TableModels cSoberTable = getSoberTable(chainObj.getClass());
                            loadNexusValue(cSoberTable, chainObj);
                        }
                        BeanUtil.setSimpleProperty(result, colName, chainObj);
                    }
                } else if (SoberEnv.OneToMany.equalsIgnoreCase(soberNexus.getMapping())) {
                    //对应id对象
                    Object findValue = BeanUtil.getProperty(result, soberNexus.getName());
                    //条件
                    String term = AnnotationUtil.getNexusTerm(result, soberNexus.getTerm());
                    //数据个数
                    int length = AnnotationUtil.getNexusLength(result, soberNexus.getLength(), getMaxRows());
                    //c查询得到列表
                    List childList = getFindFieldList(soberNexus.getTargetEntity(), soberNexus.getTargetName(), (Serializable) findValue, term,
                            AnnotationUtil.getNexusOrderBy(result, soberNexus.getOrderBy()), soberNexus.isChain(), length);
                    BeanUtil.setSimpleProperty(result, colName, childList);
                }

            }
        } catch (Exception e) {
            e.printStackTrace();
            log.error("载入关系表错误" + soberTable.toString() + "对象" + result, e);
        }
    }


    /**
     * 设置参数
     *
     * @param statement jdbc查询器
     * @param fields    查询字段
     * @param object    查询对象
     * @throws Exception 其他错误
     */
    private void setPreparedStatementValueList(PreparedStatement statement, String[] fields, Object object) throws Exception {
        for (int i = 0; i < fields.length; i++) {
            debugPrint("SetPrepared[" + (i + 1) + "]=" + BeanUtil.getProperty(object, fields[i]));
            dialect.setPreparedStatementValue(statement, i + 1, BeanUtil.getProperty(object, fields[i]));
        }
    }

    /**
     * load from id
     *
     * @param aClass       class 类对象
     * @param serializable id
     * @return query a object 返回对象
     */
    public Object get(Class aClass, Serializable serializable) throws SQLException {
        return get(aClass, serializable, false);
    }

    /**
     * ID 得到对象
     * load from id and map bean
     *
     * @param aClass       类
     * @param serializable Id
     * @return Object 得到对象
     */
    public Object get(Class aClass, Serializable serializable, boolean loadChild) throws SQLException {
        return get(aClass, null, serializable, loadChild);
    }

    /**
     * @param aClass       类
     * @param serializable 字段值
     * @return 返回对象，如果为空就创建对象，不会有null 返回
     * @throws SQLException           sql错误
     * @throws ClassNotFoundException 类不存在
     * @throws InstantiationException
     * @throws IllegalAccessException
     */
    public Object load(Class aClass, Serializable serializable) throws SQLException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        return load(aClass, null, serializable, false);
    }

    /**
     * @param aClass       类
     * @param serializable 字段值
     * @param loadChild    是否载入关联
     * @return 返回对象，如果为空就创建对象，不会有null 返回
     * @throws SQLException           sql错误
     * @throws ClassNotFoundException 类不存在
     * @throws InstantiationException
     * @throws IllegalAccessException
     */
    public Object load(Class aClass, Serializable serializable, boolean loadChild) throws SQLException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        return load(aClass, null, serializable, loadChild);
    }

    /**
     * @param aClass       类
     * @param field        字段名称
     * @param serializable 字段值
     * @param loadChild    是否载入关联
     * @return 返回对象，如果为空就创建对象，不会有null 返回
     * @throws SQLException           sql错误
     * @throws ClassNotFoundException 类不存在
     * @throws InstantiationException
     * @throws IllegalAccessException
     */
    public Object load(Class aClass, Serializable field, Serializable serializable, boolean loadChild) throws SQLException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        Object o = get(aClass, field, serializable, loadChild);
        if (o == null)
            return ClassUtil.newInstance(aClass.getName());
        return o;
    }

    /**
     * 查询字段返回一个对象
     *
     * @param aClass       类
     * @param field        字段
     * @param serializable 字段值
     * @return Object 得到对象
     */
    public Object get(Class aClass, Serializable field, Serializable serializable, boolean loadChild) throws SQLException {
        if ((serializable == null || field == null && ClassUtil.isNumberType(serializable.getClass()) && ObjectUtil.toLong(serializable) == 0))
            return null;
        //安全防范，如果有  or and where 这种关键字的，直接返回 空

        TableModels soberTable = getSoberTable(aClass);
        if (field == null) {
            field = soberTable.getPrimary();
        }

        if (!StringUtil.hasLength((String) field)) {
            throw new SQLException("SQL Primary is NULL,配置表没设置ID");
        }

        Object result = null;
        //取出cache
        String cacheName = null;
        boolean useCache = soberFactory.isUseCache() && soberTable.isUseCache();
        String cacheKey = null;
        if (useCache) {
            cacheName = StringUtil.defaultIfEmpty(soberTable.getCacheName(), soberFactory.getCacheName());
            cacheKey = SoberUtil.getLoadKey(aClass, field, serializable, loadChild);
            result = JSCacheManager.get(cacheName, cacheKey);
            if (result != null) return result;
        }
        Connection conn = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        Map<String, Object> valueMap = new HashMap<String, Object>();
        valueMap.put(Dialect.KEY_TABLE_NAME, soberTable.getTableName());
        valueMap.put(Dialect.KEY_FIELD_NAME, field);

        String sqlText = StringUtil.empty;
        try {
            conn = getConnection(SoberEnv.ReadOnly);
            if (conn == null) {
                String info = "得到链接为空，数据库连接池不能正常连接,Connection is null";
                SQLException e = new SQLException(info);
                log.error(info, e);
                throw e;
            }
            sqlText = dialect.processTemplate(Dialect.SQL_QUERY_ONE_FIELD, valueMap);
            debugPrint(sqlText);
            if (!dialect.supportsConcurReadOnly())
                preparedStatement = conn.prepareStatement(sqlText);
            else
                preparedStatement = conn.prepareStatement(sqlText, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            preparedStatement.setMaxRows(1);
            debugPrint("prepared[1]=" + serializable);
            dialect.setPreparedStatementValue(preparedStatement, 1, serializable);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                result = loadColumnsValue(soberTable, resultSet);
                //载入映射对象
                if (loadChild) {
                    loadNexusValue(soberTable, result);
                }
                //载入计算数据
                calcUnique(soberTable, result);
            }
        } catch (Exception e) {
            log.error("ERROR SQL:" + sqlText, e);
            e.printStackTrace();
            soberFactory.closeConnection(conn, true);
        } finally {
            JdbcUtil.closeResultSet(resultSet);
            JdbcUtil.closeStatement(preparedStatement);
            closeConnection(conn);
            valueMap.clear();
        }
        //放入cache
        if (useCache) {
            JSCacheManager.put(cacheName, cacheKey, result);
        }
        return result;
    }

    /**
     * @param aClass       返回实体
     * @param field        查询字段
     * @param serializable 字段值
     * @param term         条件
     * @param orderBy      排序
     * @param loadChild    是否载入映射
     * @param max          最大行数
     * @return 查询返回
     * @throws Exception 解析错误
     */

    private List getFindFieldList(Class aClass, String field, Serializable serializable, String term, String orderBy, boolean loadChild, int max) throws Exception {
        Criteria criteria = createCriteria(aClass);
        criteria = criteria.add(Expression.eq(field, serializable));
        if (!StringUtil.isNULL(term)) {
            criteria = SSqlExpression.getTermExpression(criteria, term);
        }
        if (!StringUtil.isNULL(orderBy)) {
            criteria = SSqlExpression.getSortOrder(criteria, orderBy);
        }
        criteria = criteria.setCurrentPage(1).setTotalCount(max);
        return criteria.list(loadChild);
    }

    public int save(Object object) throws Exception {
        return save(object, false);
    }

    /**
     * @param object 保存对象
     * @param child  保持子对象
     * @return 保存一个对象
     * @throws ValidException
     * @throws Exception
     */
    public int save(Object object, final boolean child) throws Exception {
        if (object == null) return -2;
        if (object instanceof Collection) {
            return save((Collection) object);
        }
        //////////配置验证才能够保存 begin

        if (soberFactory.isValid()) {
            validator(object);
        }
        //////////配置验证才能够保存 end
        TableModels soberTable = getSoberTable(object.getClass());

        PreparedStatement statement = null;

        Object idValue = BeanUtil.getProperty(object, soberTable.getPrimary());
        Map<String, Object> valueMap = new HashMap<String, Object>();
        valueMap.put(Dialect.KEY_TABLE_NAME, soberTable.getTableName());

        String[] fieldArray;
        if (idValue == null || 0 == ObjectUtil.toLong(idValue))
            fieldArray = soberTable.getFieldArray();
        else
            fieldArray = soberTable.getFullFieldArray();

        if (fieldArray == null || fieldArray.length < 1) return -2;

        valueMap.put(Dialect.KEY_FIELD_LIST, fieldArray);
        valueMap.put(Dialect.KEY_FIELD_COUNT, fieldArray.length);

        int result;
        String sqlText = StringUtil.empty;

        Connection conn = null;
        try {

            //先自动生成ID
            conn = getConnection(SoberEnv.ReadWrite);
            sqlText = dialect.processTemplate(Dialect.SQL_INSERT, valueMap);
            if (dialect.supportsGetGeneratedKeys() && soberTable.isAutoId() && soberTable.isSerial()) {
                statement = conn.prepareStatement(sqlText, Statement.RETURN_GENERATED_KEYS);
            } else {
                AnnotationUtil.autoSetId(object, soberTable.getPrimary(), getSequencesDAO());
                statement = conn.prepareStatement(sqlText, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            }
            debugPrint(sqlText);
            for (int i = 0; i < fieldArray.length; i++) {
                debugPrint("prepared[" + (i + 1) + "]=" + BeanUtil.getProperty(object, fieldArray[i]));
                dialect.setPreparedStatementValue(statement, i + 1, BeanUtil.getProperty(object, fieldArray[i]));
            }
            int temp = statement.executeUpdate();
            if (temp < 1) {
                return temp;
            } else {
                result = temp;
            }
            if (dialect.supportsGetGeneratedKeys() && soberTable.isAutoId() && soberTable.isSerial()) {
                ResultSet rs = statement.getGeneratedKeys();
                if (rs.next()) {
                    BeanUtil.setSimpleProperty(object, soberTable.getPrimary(), dialect.getResultSetValue(rs, 1));
                }
                JdbcUtil.closeResultSet(rs);
            }
            if (child) {
                Object keyObj = BeanUtil.getProperty(object, soberTable.getPrimary());
                /////////////////////////////保存关联对象begin
                Map<String, SoberNexus> nexus = soberTable.getNexusMap();
                for (String colName : nexus.keySet()) {

                    SoberNexus soberNexus = nexus.get(colName);
                    if (!soberNexus.isSave()) continue;
                    if (SoberEnv.OneToOne.equalsIgnoreCase(soberNexus.getMapping())) {
                        Object oneToOneObject = BeanUtil.getProperty(object, colName);
                        if (oneToOneObject == null) continue;
                        BeanUtil.setSimpleProperty(oneToOneObject, soberNexus.getTargetName(), keyObj);
                        result = result + save(oneToOneObject, false);
                    }
                    if (SoberEnv.OneToMany.equalsIgnoreCase(soberNexus.getMapping())) {
                        Collection oneToMayObjects = (Collection) BeanUtil.getProperty(object, colName);
                        if (oneToMayObjects == null || oneToMayObjects.isEmpty()) continue;
                        for (Object o : oneToMayObjects) {
                            BeanUtil.setSimpleProperty(o, soberNexus.getTargetName(), keyObj);
                        }
                        int s = save(oneToMayObjects);
                        if (s != oneToMayObjects.size()) return -2;
                        result = result + oneToMayObjects.size();
                        oneToMayObjects.clear();
                    }
                }
                //save
                /////////////////////////////保存关联对象end
            }
            return result;
        } catch (Exception e) {
            log.error("SQL:" + sqlText, e);
            System.err.println(sqlText);
            e.printStackTrace();
            soberFactory.closeConnection(conn, true);
            return -2;
        } finally {
            JdbcUtil.closeStatement(statement);
            closeConnection(conn);
            valueMap.clear();
        }
    }

    /**
     * @param collection 保持一个列表
     * @return 返回保持数量
     * @throws Exception      验证错误
     * @throws ValidException 其他错误
     */
    public int save(Collection collection) throws Exception {
        if (collection == null || collection.size() < 1) return -2;
        TableModels soberTable = null;
        int result = 0;
        for (Object obj : collection) {
            if (soberTable == null) {
                soberTable = getSoberTable(obj.getClass());
            }
            //////////配置验证才能够保存 begin
            if (soberFactory.isValid()) {
                validator(obj);
            }
            //////////配置验证才能够保存 end
            result = result + save(obj);
        }
        return result;
    }

    /**
     * @param collection 批量快速保持
     * @return jdbc 更新返回状态
     * @throws Exception
     */
    public int batchSave(Collection collection) throws Exception {
        if (collection == null || collection.size() < 1) return -2;
        Object checkObj = collection.iterator().next();
        TableModels soberTable = getSoberTable(checkObj.getClass());
        PreparedStatement statement = null;
        Object idValue = BeanUtil.getProperty(checkObj, soberTable.getPrimary());
        Map<String, Object> valueMap = new HashMap<String, Object>();
        valueMap.put(Dialect.KEY_TABLE_NAME, soberTable.getTableName());

        String[] fieldArray;
        if (idValue == null || 0 == ObjectUtil.toLong(idValue))
            fieldArray = soberTable.getFieldArray();
        else
            fieldArray = soberTable.getFullFieldArray();
        if (fieldArray == null || fieldArray.length < 1) return -2;
        valueMap.put(Dialect.KEY_FIELD_LIST, fieldArray);
        valueMap.put(Dialect.KEY_FIELD_COUNT, fieldArray.length);

        int result = 0;
        String sqlText = StringUtil.empty;
        Connection conn = getConnection(SoberEnv.WriteOnly);
        boolean oldAutoCommit = conn.getAutoCommit();
        try {
            //先自动生成ID
            conn.setAutoCommit(false);
            sqlText = dialect.processTemplate(Dialect.SQL_INSERT, valueMap);
            debugPrint(sqlText);
            statement = conn.prepareStatement(sqlText, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            int cm = 0;
            for (Object object : collection) {
                cm++;
                //////////配置验证才能够保存 begin
                if (soberFactory.isValid()) {
                    validator(object);
                }
                //////////配置验证才能够保存 end
                for (int i = 0; i < fieldArray.length; i++) {
                    debugPrint("prepared[" + (i + 1) + "]=" + BeanUtil.getProperty(object, fieldArray[i]));
                    dialect.setPreparedStatementValue(statement, i + 1, BeanUtil.getProperty(object, fieldArray[i]));
                }
                statement.addBatch();
                if (cm % 500 == 0) {
                    result = result + ArrayUtil.sum(statement.executeBatch());
                    conn.commit();
                }
            }
            if (cm % 500 != 0) {
                result = result + ArrayUtil.sum(statement.executeBatch());
                conn.commit();
            }
            return result;
        } catch (Exception e) {
            log.error("ERROR SQL:" + sqlText, e);
            e.printStackTrace();
            soberFactory.closeConnection(conn, true);
            return -2;
        } finally {
            conn.setAutoCommit(oldAutoCommit);
            JdbcUtil.closeStatement(statement);
            closeConnection(conn);
            valueMap.clear();
        }
    }

    /**
     * 级联删除,不删除ManyToOne,只删除OneToOne 和 OneToMany
     *
     * @param o        对象
     * @param delChild 是否删除子对象
     * @return boolean 是否成功
     */
    public int delete(Object o, boolean delChild) throws Exception {
        if (o == null) return -2;
        TableModels soberTable = getSoberTable(o.getClass());
        return delete(o.getClass(), soberTable.getPrimary(), (Serializable) BeanUtil.getProperty(o, soberTable.getPrimary()), null, delChild);
    }

    /**
     * @param aClass   类
     * @param ids      id 列表
     * @param delChild 删除关联
     * @return 删除
     */
    public int delete(Class aClass, Object[] ids, boolean delChild) {
        if (ids == null) return -2;
        TableModels soberTable = getSoberTable(aClass);
        return createCriteria(aClass).add(Expression.in(soberTable.getPrimary(), ids)).delete(delChild);
    }

    /**
     * 删除对象
     *
     * @param o 对象
     * @return boolean
     */
    public int delete(Object o) throws Exception {
        if (o == null) return -2;
        TableModels soberTable = getSoberTable(o.getClass());
        Object key = BeanUtil.getProperty(o, soberTable.getPrimary());
        return delete(o.getClass(), soberTable.getPrimary(), (Serializable) key);
    }

    /**
     * @param aClass       类
     * @param serializable id
     * @return 删除对象
     */
    public int delete(Class aClass, Serializable serializable) {

        if (serializable.getClass().isArray()) {
            int length = java.lang.reflect.Array.getLength(serializable);
            Object[] params = new Object[length];
            for (int i = 0; i < length; i++) {
                params[i] = java.lang.reflect.Array.get(serializable, i);
            }
            return delete(aClass, params, false);
        }
        return delete(aClass, getSoberTable(aClass).getPrimary(), serializable);
    }

    /**
     * 更具字段删除一个对象,或一组对象
     *
     * @param aClass       类
     * @param field        字段
     * @param serializable 字段值
     * @return 是否成功
     */
    public int delete(Class aClass, String field, Serializable serializable) {
        return delete(aClass, field, serializable, null, false);
    }

    /**
     * 级联删除,不删除ManyToOne,只删除OneToOne 和 OneToMany
     *
     * @param aClass       删除的类
     * @param serializable 值
     * @param delChild     是否删除映射对象
     * @return boolean 是否成功
     * @throws Exception 删除错误
     */
    public int delete(Class aClass, Serializable serializable, boolean delChild) throws Exception {
        return delete(aClass, getSoberTable(aClass).getPrimary(), serializable, null, delChild);
    }

    /**
     * 级联方式删除对象,只删除一层
     *
     * @param aClass       删除对象
     * @param field        删除字段
     * @param serializable 字段值
     * @param delChild     删除映射对象
     * @return boolean
     */
    public int delete(Class aClass, String field, Serializable serializable, String term, boolean delChild) {

        Criteria criteria = createCriteria(aClass);
        if (serializable.getClass().isArray()) {
            int length = java.lang.reflect.Array.getLength(serializable);
            Object[] params = new Object[length];
            for (int i = 0; i < length; i++) {
                params[i] = java.lang.reflect.Array.get(serializable, i);
            }
            criteria = criteria.add(Expression.in(field, params));
        } else {
            criteria = criteria.add(Expression.eq(field, serializable));
        }
        if (!StringUtil.isNULL(term)) {
            try {
                criteria = SSqlExpression.getTermExpression(criteria, term);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return criteria.delete(delChild);
    }

    /**
     * 删除映射关系的对象 ManyToOne 关系不删除
     *
     * @param o 对象
     * @return boolean 是否成功
     */
    public int deleteNexus(Object o) {
        int result = 0;
        TableModels soberTable = getSoberTable(o.getClass());
        for (SoberNexus soberNexus : soberTable.getNexusMap().values()) {
            if (!soberNexus.isDelete()) continue;
            if (!SoberEnv.ManyToOne.equalsIgnoreCase(soberNexus.getMapping())) {
                Object selfValue;
                try {
                    selfValue = BeanUtil.getProperty(o, soberNexus.getName());
                    if (selfValue == null) continue;
                } catch (Exception e) {
                    e.printStackTrace();
                    log.error("映射关系错误:" + o.getClass().getName() + "  方法:" + soberNexus.getName() + "不存在", e);
                    return -2;
                }
                result = result + delete(soberNexus.getTargetEntity(), soberNexus.getTargetName(), (Serializable) selfValue);
            }
        }
        return result;
    }

    //------------------------------------------------------------------------------------------------------------------

    public int update(Collection collection) throws Exception {
        int result = 0;
        for (Object o : collection) {
            result = result + update(o);
        }
        return result;
    }

    /**
     * 更具ID更新一个对象
     *
     * @param object 对象
     * @return boolean
     */
    public int update(Object object) throws Exception {
        if (object == null) return -2;
        //////////配置验证才能够保存 begin

        if (soberFactory.isValid()) {
            validator(object);
        }

        //////////配置验证才能够保存 end                ;
        TableModels soberTable = getSoberTable(object.getClass());
        if (soberTable == null) return -2;
        Connection conn = null;
        PreparedStatement statement = null;
        String[] fieldArray = soberTable.getFieldArray();
        if (ArrayUtil.isEmpty(fieldArray)) return -2;
        fieldArray = ArrayUtil.delete(fieldArray, soberTable.getPrimary(), true);

        Map<String, Object> valueMap = new HashMap<String, Object>();
        valueMap.put(Dialect.KEY_TABLE_NAME, soberTable.getTableName());
        valueMap.put(Dialect.KEY_FIELD_LIST, fieldArray);
        valueMap.put(Dialect.KEY_FIELD_COUNT, fieldArray.length);
        valueMap.put(Dialect.KEY_FIELD_NAME, soberTable.getPrimary());
        valueMap.put(Dialect.KEY_FIELD_NAME + Dialect.FIELD_QUOTE, JdbcUtil.isQuote(soberTable, soberTable.getPrimary()));
        Object value = null;
        try {
            value = BeanUtil.getProperty(object, soberTable.getPrimary());
            valueMap.put(Dialect.KEY_FIELD_VALUE, value);
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (value == null) {
            SQLException e = new SQLException("ERROR:SQL,update primary is null,更新的关键字不能为空!");
            log.error(ObjectUtil.toString(object), e);
            e.printStackTrace();
            throw e;
        }

        int result = -2;
        String sqlText = StringUtil.empty;
        try {
            conn = getConnection(SoberEnv.ReadWrite);
            sqlText = dialect.processTemplate(Dialect.SQL_UPDATE, valueMap);
            debugPrint(sqlText);
            if (!dialect.supportsConcurReadOnly()) {
                statement = conn.prepareStatement(sqlText);
            } else
                statement = conn.prepareStatement(sqlText, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

            setPreparedStatementValueList(statement, fieldArray, object);
            result = statement.executeUpdate();

            ///////////////////处理关联对象begin
            Map<String, SoberNexus> nexus = soberTable.getNexusMap();
            for (String colName : nexus.keySet()) {
                SoberNexus soberNexus = nexus.get(colName);
                if (!soberNexus.isUpdate()) continue;

                if (SoberEnv.OneToOne.equalsIgnoreCase(soberNexus.getMapping())) {
                    Object findValue = BeanUtil.getProperty(object, soberNexus.getName());
                    if (findValue == null) continue;
                    delete(soberNexus.getTargetEntity(), soberNexus.getTargetName(), (Serializable) findValue);
                    Object saveObj = BeanUtil.getProperty(object, colName);
                    if (saveObj != null) {
                        result = result + save(saveObj);
                    }
                }
                if (SoberEnv.OneToMany.equalsIgnoreCase(soberNexus.getMapping())) {
                    Object findValue = BeanUtil.getProperty(object, soberNexus.getName());
                    if (findValue == null) continue;
                    Criteria criteria = createCriteria(soberNexus.getTargetEntity()).add(Expression.eq(soberNexus.getTargetName(), findValue));
                    SSqlExpression.getTermExpression(criteria, soberNexus.getTerm()).delete(false);
                    Collection saveObj = (Collection) BeanUtil.getProperty(object, colName);
                    if (saveObj != null && !saveObj.isEmpty()) {
                        result = result + save(saveObj);
                        evict(soberNexus.getTargetEntity());
                    }
                }
            }
            ///////////////////处理关联对象end


        } catch (Exception e) {
            log.error("ERROR SQL:" + sqlText, e);
            e.printStackTrace();
            soberFactory.closeConnection(conn, true);
            return result;
        } finally {
            JdbcUtil.closeStatement(statement);
            closeConnection(conn);
            valueMap.clear();
        }

        return result;
    }

    /**
     * @param object      查询对象
     * @param updateFiled 更新一个字段
     * @return 指定更新字段, 特殊不验证了
     */
    public int update(Object object, String[] updateFiled) {
        if (object == null) return -2;
        TableModels soberTable = getSoberTable(object.getClass());
        Connection conn = null;
        PreparedStatement statement = null;
        Map<String, Object> valueMap = new HashMap<String, Object>();
        valueMap.put(Dialect.KEY_TABLE_NAME, soberTable.getTableName());
        valueMap.put(Dialect.KEY_FIELD_LIST, updateFiled);
        valueMap.put(Dialect.KEY_FIELD_COUNT, updateFiled.length);
        valueMap.put(Dialect.KEY_FIELD_NAME, soberTable.getPrimary());
        valueMap.put(Dialect.KEY_FIELD_NAME + Dialect.FIELD_QUOTE, JdbcUtil.isQuote(soberTable, soberTable.getPrimary()));
        Object value;
        String sqlText = StringUtil.empty;
        try {
            value = BeanUtil.getProperty(object, soberTable.getPrimary());
            valueMap.put(Dialect.KEY_FIELD_VALUE, value);
            if (valueMap.get(Dialect.KEY_FIELD_VALUE) == null) {
                throw new SQLException("SQL Primary Key is NULL");
            }
            conn = getConnection(SoberEnv.ReadWrite);
            sqlText = dialect.processTemplate(Dialect.SQL_UPDATE, valueMap);
            debugPrint(sqlText);
            if (!dialect.supportsConcurReadOnly()) {
                statement = conn.prepareStatement(sqlText);
            } else {
                statement = conn.prepareStatement(sqlText, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
            }
            //TYPE_FORWARD_ONLY
            setPreparedStatementValueList(statement, updateFiled, object);
            return statement.executeUpdate();
        } catch (Exception e) {
            log.error("ERROR SQL:" + sqlText, e);
            soberFactory.closeConnection(conn, true);
            return -2;
        } finally {
            valueMap.clear();
            JdbcUtil.closeStatement(statement);
            closeConnection(conn);
        }
    }

    public int update(String sql) {
        return update(sql, (Object[]) null);
    }

    /**
     * 更新
     *
     * @param cla     类
     * @param sqlText sql
     * @param params  参数
     * @return 更新数量
     */
    @SuppressWarnings("unchecked")
    public int update(Class cla, String sqlText, Object params) throws IOException {
        Map<String, Object> valueMap;
        if (params instanceof Map) {
            valueMap = (Map<String, Object>) params;
        } else if (params instanceof Collection) {
            Collection coll = (Collection) params;
            return update(sqlText, coll.toArray());
        } else {
            valueMap = BeanUtil.toMap(params);
        }
        if (valueMap == null) {
            valueMap = new HashMap<String, Object>();
        }
        TableModels soberTable = getSoberTable(cla);
        valueMap.put(Dialect.KEY_TABLE_NAME, soberTable.getTableName());
        valueMap.put(Dialect.KEY_PRIMARY_KEY, soberTable.getPrimary());
        try {
            return update(dialect.processSQL(sqlText, valueMap), (Object[]) params);
        } catch (Exception e) {
            log.error("soberTable:" + soberTable.toString(), e);
        }
        return -2;
    }

    /**
     * @param sqlText 使用sql直接更新
     * @param params  参数
     * @return 更新数量
     */
    public int update(String sqlText, Object[] params) {
        if (sqlText == null || sqlText.length() < 1) return -2;
        int result = -1;
        Connection conn = null;
        PreparedStatement statement = null;
        try {
            conn = getConnection(SoberEnv.ReadWrite);
            debugPrint(sqlText);
            if (!dialect.supportsConcurReadOnly()) {
                statement = conn.prepareStatement(sqlText);
            } else
                statement = conn.prepareStatement(sqlText, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
            if (!ArrayUtil.isEmpty(params)) {
                for (int i = 0; i < params.length; i++) {
                    debugPrint("prepared[" + (i + 1) + "]=" + params[i]);
                    dialect.setPreparedStatementValue(statement, i + 1, params[i]);
                }
            }
            result = statement.executeUpdate();
        } catch (Exception e) {
            log.error("SQL:" + sqlText, e);
            soberFactory.closeConnection(conn, true);
        } finally {
            JdbcUtil.closeStatement(statement);
            closeConnection(conn);
        }
        return result;
    }

    public boolean execute(String sqlText) {
        return execute(sqlText, null);
    }

    /**
     * 执行一个sql
     *
     * @param cla     类 对映配置中的map命名空间
     * @param sqlText sql
     * @param params  支持类型 Object[],HashMap<String,Object>
     * @return 执行情况
     * @throws IOException io错误
     */
    @SuppressWarnings("unchecked")
    public boolean execute(Class cla, String sqlText, Object params) throws IOException {
        Map<String, Object> valueMap = null;
        TableModels soberTable = getSoberTable(cla);
        if (params instanceof Map) {
            valueMap = (Map<String, Object>) params;
        } else if (params instanceof Collection) {
            Collection coll = (Collection) params;
            params = coll.toArray();
        }
        if (valueMap == null) valueMap = new HashMap<String, Object>();
        valueMap.put(Dialect.KEY_TABLE_NAME, soberTable.getTableName());
        valueMap.put(Dialect.KEY_PRIMARY_KEY, soberTable.getPrimary());
        try {
            return execute(dialect.processSQL(sqlText, valueMap), (Object[]) params);
        } catch (Exception e) {
            log.error("soberTable:" + soberTable.toString() + ",SQL:" + sqlText, e);
        }
        return false;
    }

    /**
     * 执行一个 execute
     * execute 方法应该仅在语句能返回多个 ResultSet 对象、多个更新计数或ResultSet 对象与更新计数的组合时使用。当执行某个已存储过程或动态执行未知 SQL 字符串（即应用程序程序员在编译时未知）时，有可能出现多个结果的情况，尽管这种情况很少见。例如，用户可能执行一个已存储过程（使用 CallableStatement 对象 - 参见第 135 页的 CallableStatement），并且该已存储过程可执行更新，然后执行选择，再进行更新，再进行选择，等等。通常使用已存储过程的人应知道它所返回的内容。
     * 因为方法 execute 处理非常规情况，所以获取其结果需要一些特殊处理并不足为怪。例如，假定已知某个过程返回两个结果集，则在使用方法 execute 执行该过程后，必须调用方法 getResultSet 获得第一个结果集，然后调用适当的getXXX 方法获取其中的值。要获得第二个结果集，需要先调用 getMoreResults方法，然后再调用 getResultSet 方法。如果已知某个过程返回两个更新计数，则首先调用方法 getUpdateCount，然后调用 getMoreResults，并再次调用
     *
     * @param sqlText sql
     * @param params  参数
     * @return 执行结果
     */
    public boolean execute(String sqlText, Object[] params) {
        if (sqlText == null || sqlText.length() < 1) return false;
        Connection conn = null;
        PreparedStatement statement = null;
        try {
            conn = getConnection(SoberEnv.WriteOnly);
            debugPrint(sqlText);
            if (!dialect.supportsConcurReadOnly()) {
                statement = conn.prepareStatement(sqlText);
            } else {
                statement = conn.prepareStatement(sqlText, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
            }

            if (params != null) {
                for (int i = 0; i < params.length; i++) {
                    debugPrint("prepared[" + (i + 1) + "]=" + params[i]);
                    dialect.setPreparedStatementValue(statement, i + 1, params[i]);
                }
            }
            return statement.execute();
        } catch (Exception e) {
            log.error("SQL:" + sqlText, e);
            soberFactory.closeConnection(conn, true);
            return false;
        } finally {
            JdbcUtil.closeStatement(statement);
            closeConnection(conn);
        }
    }

    /**
     * 先判断是否存在,存在就使用更新,否则增加
     *
     * @param object 对象
     * @return boolean 保存是否成功
     */
    public int saveOrUpdate(Object object) throws Exception {
        if (object == null) return -2;
        if (object instanceof Collection) {
            return saveOrUpdateAll((Collection) object);
        }

        //////////配置验证才能够保存 begin
        if (soberFactory.isValid()) {
            validator(object);
        }
        //////////配置验证才能够保存 end

        TableModels soberTable = getSoberTable(object.getClass());
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        String[] fieldArray = soberTable.getFieldArray();
        if (fieldArray == null || fieldArray.length < 1) return -2;
        Map<String, Object> valueMap = new HashMap<String, Object>();
        valueMap.put(Dialect.KEY_TABLE_NAME, soberTable.getTableName());
        valueMap.put(Dialect.KEY_FIELD_LIST, fieldArray);
        valueMap.put(Dialect.KEY_FIELD_COUNT, fieldArray.length);
        valueMap.put(Dialect.KEY_FIELD_NAME, soberTable.getPrimary());
        valueMap.put(Dialect.KEY_FIELD_NAME + Dialect.FIELD_QUOTE, JdbcUtil.isQuote(soberTable, soberTable.getPrimary()));
        valueMap.put(Dialect.KEY_FIELD_VALUE, BeanUtil.getProperty(object, soberTable.getPrimary()));
        if (valueMap.get(Dialect.KEY_FIELD_VALUE) == null) {
            if (dialect.supportsGetGeneratedKeys() && soberTable.isAutoId() && soberTable.isSerial()) {
                return save(object);
            } else {
                throw new SQLException("SQL Primary is NULL");
            }
        }
        String sqlText = StringUtil.empty;
        Connection conn = null;

        try {

            conn = getConnection(SoberEnv.ReadWrite);
            sqlText = dialect.processTemplate(Dialect.SQL_HAVE, valueMap);
            if (!dialect.supportsConcurReadOnly())
                statement = conn.prepareStatement(sqlText);
            else
                statement = conn.prepareStatement(sqlText, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
            debugPrint(sqlText);
            resultSet = statement.executeQuery();
            if (resultSet.next() && resultSet.getInt(1) > 0) {
                fieldArray = ArrayUtil.delete(fieldArray, soberTable.getPrimary(), true);
                valueMap.put(Dialect.KEY_FIELD_LIST, fieldArray);
                valueMap.put(Dialect.KEY_FIELD_COUNT, fieldArray.length);
                sqlText = dialect.processTemplate(Dialect.SQL_UPDATE, valueMap);
                debugPrint(sqlText);
                statement = conn.prepareStatement(sqlText);
                setPreparedStatementValueList(statement, fieldArray, object);
            } else {
                sqlText = dialect.processTemplate(Dialect.SQL_INSERT, valueMap);
                debugPrint(sqlText);

                if (!dialect.supportsConcurReadOnly()) {
                    statement = conn.prepareStatement(sqlText);
                } else {
                    statement = conn.prepareStatement(sqlText, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
                }
                setPreparedStatementValueList(statement, fieldArray, object);
            }
            return statement.executeUpdate();
        } catch (Exception e) {
            log.error("SQL:" + sqlText, e);
            soberFactory.closeConnection(conn, true);
            return -2;
        } finally {
            valueMap.clear();
            JdbcUtil.closeResultSet(resultSet);
            JdbcUtil.closeStatement(statement);
            closeConnection(conn);

        }

    }

    /**
     * 先判断是否存在,存在就使用更新,否则增加 ,处理一个队
     *
     * @param collection 更新对象列表
     * @return boolean 返回是否成功
     * @throws Exception                               执行错误
     * @throws com.jspx.sober.exception.ValidException 验证错误
     */
    private int saveOrUpdateAll(Collection collection) throws Exception {
        if (collection == null || collection.isEmpty()) return -2;
        Connection conn = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        TableModels soberTable = null;
        List<Object> deleteId = new ArrayList<Object>();
        //得到类型
        int i = 0;
        for (Object object : collection) {
            if (i == 0) {
                soberTable = getSoberTable(object.getClass());
                if (soberTable == null) return -2;
            }
            deleteId.add(BeanUtil.getProperty(object, soberTable.getPrimary()));
            i++;
            //////////配置验证才能够保存 begin

            if (soberFactory.isValid()) {
                validator(object);
            }
            ////////////配置验证才能够保存 end

        }
        if (soberTable == null) return -2;
        String[] fieldArray = soberTable.getFieldArray();
        if (fieldArray == null || fieldArray.length < 1) return -2;
        Map<String, Object> valueMap = new HashMap<String, Object>();
        valueMap.put(Dialect.KEY_TABLE_NAME, soberTable.getTableName());
        valueMap.put(Dialect.KEY_FIELD_LIST, fieldArray);
        valueMap.put(Dialect.KEY_FIELD_COUNT, fieldArray.length);
        valueMap.put(Dialect.KEY_FIELD_NAME, soberTable.getPrimary());
        valueMap.put(Dialect.KEY_FIELD_NAME + Dialect.FIELD_QUOTE, JdbcUtil.isQuote(soberTable, soberTable.getPrimary()));
        int result = 0;
        String sqlText = StringUtil.empty;
        try {
            conn = getConnection(SoberEnv.ReadWrite);

            //////////////删除 begin
            valueMap.put(Dialect.KEY_FIELD_VALUE, deleteId);
            sqlText = dialect.processTemplate(Dialect.SQL_DELETE_IN, valueMap);
            debugPrint(sqlText);
            if (!dialect.supportsConcurReadOnly()) {
                conn.prepareStatement(sqlText).execute();
            } else {
                conn.prepareStatement(sqlText, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE).execute();
            }
            //////////////删除 end

            sqlText = dialect.processTemplate(Dialect.SQL_INSERT, valueMap);
            debugPrint(sqlText);
            if (!dialect.supportsConcurReadOnly()) {
                statement = conn.prepareStatement(sqlText);
            } else
                statement = conn.prepareStatement(sqlText, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
            for (Object object : collection) {
                setPreparedStatementValueList(statement, fieldArray, object);
                int temp = statement.executeUpdate();
                if (temp < 1) {
                    throw new SQLException(sqlText + " object:" + MapUtil.toString(BeanUtil.toMap(object)));
                } else {
                    result = result + temp;
                }
                statement.clearParameters();
            }
            return result;
        } catch (Exception e) {
            log.error("SQL:" + sqlText, e);
            soberFactory.closeConnection(conn, true);
            return -2;
        } finally {
            valueMap.clear();
            JdbcUtil.closeResultSet(resultSet);
            JdbcUtil.closeStatement(statement);
            closeConnection(conn);

        }
    }

    //------------------------------------------------------------------------------------------------------------------

    /**
     * 查询返回列表
     * 使用jdbc完成,比较浪费资源
     *
     * @param cla         class
     * @param sqlText     sql
     * @param parm        参数
     * @param currentPage page number
     * @param totalCount  rows
     * @param loadChild   load map object
     * @return query object list
     */
    @SuppressWarnings("unchecked")
    public List queryForList(Class cla, String sqlText, Object[] parm, int currentPage, int totalCount, boolean loadChild) {
        if (totalCount > getMaxRows()) totalCount = getMaxRows();
        if (currentPage <= 0) currentPage = 1;
        int iEnd = currentPage * totalCount;
        if (iEnd < 0) iEnd = 0;
        int iBegin = iEnd - totalCount;
        if (iBegin < 0) iBegin = 0;

        TableModels soberTable = getSoberTable(cla);
        Connection conn = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List result = null;
        Map<String, Object> valueMap = new HashMap<String, Object>(3);
        valueMap.put(Dialect.KEY_TABLE_NAME, soberTable.getTableName());
        valueMap.put(Dialect.KEY_PRIMARY_KEY, soberTable.getPrimary());
        try {
            conn = getConnection(SoberEnv.ReadOnly);
            sqlText = dialect.processSQL(sqlText, valueMap);
            debugPrint(sqlText);

            if (!dialect.supportsConcurReadOnly())
                statement = conn.prepareStatement(sqlText);
            else
                statement = conn.prepareStatement(sqlText, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

            statement.setMaxRows(iEnd);
            if (parm != null) {
                for (int i = 0; i < parm.length; i++) {
                    debugPrint("prepared[" + (i + 1) + "]=" + parm[i]);
                    dialect.setPreparedStatementValue(statement, i + 1, parm[i]);
                }
            }

            resultSet = statement.executeQuery();
            result = new ArrayList();
            if (iBegin > 0) resultSet.absolute(iBegin);
            while (resultSet.next()) {

                Object tempObj = loadColumnsValue(soberTable, resultSet);
                //载入计算数据
                calcUnique(soberTable, tempObj);
                result.add(tempObj);
                if (result.size() > totalCount) break;
            }
            if (loadChild) {
                loadNexusList(soberTable, result);
            }
        } catch (Exception e) {
            log.error(soberTable.toString() + ",SQL:" + sqlText, e);
            soberFactory.closeConnection(conn, true);
        } finally {
            JdbcUtil.closeResultSet(resultSet);
            JdbcUtil.closeStatement(statement);
            closeConnection(conn);
            valueMap.clear();
        }
        return result;
    }

    /**
     * @param sqlText     sql
     * @param param       参数数组
     * @param currentPage 页数
     * @param totalCount  返回行数
     * @return List  查询返回列表
     */
    @SuppressWarnings("unchecked")
    public List<DataMap<String, Object>> queryForList(String sqlText, Object[] param, int currentPage, int totalCount) {
        if (totalCount > getMaxRows()) totalCount = getMaxRows();
        if (currentPage <= 0) currentPage = 1;
        int iEnd = currentPage * totalCount;
        if (iEnd < 0) iEnd = 0;
        int iBegin = iEnd - totalCount;
        Connection conn = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<DataMap<String, Object>> result = null;

        try {
            conn = getConnection(SoberEnv.ReadOnly);
            debugPrint(sqlText);

            if (!dialect.supportsConcurReadOnly())
                statement = conn.prepareStatement(sqlText);
            else
                statement = conn.prepareStatement(sqlText, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

            statement.setMaxRows(iEnd);
            if (param != null) {
                for (int i = 0; i < param.length; i++) {
                    debugPrint("prepared[" + (i + 1) + "]=" + param[i]);
                    dialect.setPreparedStatementValue(statement, i + 1, param[i]);
                }
            }
            resultSet = statement.executeQuery();
            result = new ArrayList<DataMap<String, Object>>();
            if (iBegin == 0 || resultSet.absolute(iBegin)) {
                ResultSetMetaData metaData = resultSet.getMetaData();
                while (resultSet.next()) {
                    DataMap<String, Object> valueMap = new DataMap<String, Object>();
                    for (int n = 1; n <= metaData.getColumnCount(); n++) {
                        valueMap.put(metaData.getColumnLabel(n), dialect.getResultSetValue(resultSet, n));
                    }
                    result.add(valueMap);
                    if (result.size() > totalCount) break;
                }
            }
        } catch (Exception e) {
            log.error("SQL:" + sqlText, e);
            soberFactory.closeConnection(conn, true);
        } finally {
            JdbcUtil.closeResultSet(resultSet);
            JdbcUtil.closeStatement(statement);
            closeConnection(conn);
        }
        return result;
    }
    //------------------------------------------------------------------------------------------------------------------

    /**
     * @param cla 类
     * @param sql sql
     * @param o   对象
     * @return 返回单一对象
     * @throws Exception 错误
     */
    public Object getUniqueResult(Class cla, String sql, Object o) throws Exception {
        Map<String, Object> valueMap = BeanUtil.toMap(o);
        TableModels soberTable = getSoberTable(cla);
        valueMap.put(Dialect.KEY_TABLE_NAME, soberTable.getTableName());
        valueMap.put(Dialect.KEY_PRIMARY_KEY, soberTable.getPrimary());
        return getUniqueResult(sql, valueMap);
    }

    /**
     * @param sql sql语句
     * @param o   参数对象
     * @return 单一返回对象
     * @throws Exception
     */
    public Object getUniqueResult(String sql, Object o) throws Exception {
        Map<String, Object> valueMap = null;
        if (o != null) {
            valueMap = BeanUtil.toMap(o);
            TableModels soberTable = getSoberTable(o.getClass());
            valueMap.put(Dialect.KEY_TABLE_NAME, soberTable.getTableName());
            valueMap.put(Dialect.KEY_PRIMARY_KEY, soberTable.getPrimary());
        }
        return getUniqueResult(sql, valueMap);
    }

    /**
     * @param sql sql语句
     * @return 单一返回对象
     * @throws Exception
     */
    public Object getUniqueResult(String sql) throws Exception {
        return getUniqueResult(sql, (Object) null);
    }

    /**
     * @param sqlText sql语句
     * @param param   参数数组
     * @return 单一返回对象
     * @throws Exception
     */
    public Object getUniqueResult(String sqlText, Object[] param) throws Exception {
        Object result = null;
        Connection conn = null;
        ResultSet resultSet = null;
        PreparedStatement statement = null;
        try {
            conn = getConnection(SoberEnv.ReadOnly);
            if (!dialect.supportsConcurReadOnly())
                statement = conn.prepareStatement(sqlText);
            else
                statement = conn.prepareStatement(sqlText, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);

            debugPrint(sqlText);
            if (!ArrayUtil.isEmpty(param)) {
                for (int i = 0; i < param.length; i++) {
                    debugPrint("prepared[" + (i + 1) + "]=" + param[i]);
                    dialect.setPreparedStatementValue(statement, i + 1, param[i]);
                }
            }
            resultSet = statement.executeQuery();
            if (resultSet.next()) {
                result = resultSet.getObject(1);
            }
        } catch (Exception e) {
            log.error("SQL:" + sqlText, e);
            soberFactory.closeConnection(conn, true);
        } finally {
            JdbcUtil.closeResultSet(resultSet);
            JdbcUtil.closeStatement(statement);
            closeConnection(conn);
        }

        return result;
    }

    /**
     * 单个对象查询返回
     *
     * @param sql      sql
     * @param valueMap map参数
     * @return Object
     * @throws Exception
     */
    @SuppressWarnings("unchecked")
    public Object getUniqueResult(String sql, Map<String, Object> valueMap) throws Exception {
        Connection conn = null;
        Statement statement = null;
        ResultSet resultSet = null;
        String sqlText = StringUtil.empty;
        try {
            sqlText = dialect.processSQL(sql, valueMap);
            debugPrint(sqlText);
            conn = getConnection(SoberEnv.ReadOnly);

            if (!dialect.supportsConcurReadOnly())
                statement = conn.createStatement();
            else
                statement = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);

            resultSet = statement.executeQuery(sqlText);
            if (resultSet.next()) {

                return dialect.getResultSetValue(resultSet, 1);
            }
        } catch (Exception e) {
            log.error("SQL:" + sqlText, e);
            soberFactory.closeConnection(conn, true);
        } finally {
            JdbcUtil.closeResultSet(resultSet);
            JdbcUtil.closeStatement(statement);
            closeConnection(conn);
        }
        return null;
    }

    /**
     * 删除一堆对象
     *
     * @param collection 删除激活
     * @return boolean 判断成功
     * @throws Exception
     */
    public boolean deleteAll(Collection collection) throws Exception {
        if (collection == null) return false;
        PreparedStatement statement = null;
        Connection conn = null;
        ResultSet resultSet = null;
        TableModels soberTable = null;
        List<Object> deleteId = new ArrayList<Object>();
        int i = 0;
        for (Object object : collection) {
            if (i == 0) {
                soberTable = getSoberTable(object.getClass());
                if (soberTable == null) return false;
            }
            deleteId.add(BeanUtil.getProperty(object, soberTable.getPrimary()));
            i++;
        }

        if (soberTable == null) return false;
        String[] fieldArray = soberTable.getFieldArray();
        if (fieldArray == null || fieldArray.length < 1) return false;
        Map<String, Object> valueMap = new HashMap<String, Object>();
        valueMap.put(Dialect.KEY_TABLE_NAME, soberTable.getTableName());
        valueMap.put(Dialect.KEY_FIELD_LIST, fieldArray);
        valueMap.put(Dialect.KEY_FIELD_COUNT, fieldArray.length);
        valueMap.put(Dialect.KEY_FIELD_NAME, soberTable.getPrimary());
        valueMap.put(Dialect.KEY_FIELD_NAME + Dialect.FIELD_QUOTE, JdbcUtil.isQuote(soberTable, soberTable.getPrimary()));
        String sqlText = StringUtil.empty;
        try {
            valueMap.put(Dialect.KEY_FIELD_VALUE, deleteId);
            conn = getConnection(SoberEnv.WriteOnly);
            sqlText = dialect.processTemplate(Dialect.SQL_DELETE_IN, valueMap);
            debugPrint(sqlText);
            if (!dialect.supportsConcurReadOnly()) {
                statement = conn.prepareStatement(sqlText);
            } else {
                statement = conn.prepareStatement(sqlText, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
            }
            return statement.execute();
        } catch (Exception e) {
            log.error("SQL:" + sqlText, e);
            soberFactory.closeConnection(conn, true);
            return false;
        } finally {
            valueMap.clear();
            JdbcUtil.closeResultSet(resultSet);
            JdbcUtil.closeStatement(statement);
            closeConnection(conn);
        }
    }

    /**
     * 得到创建表的SQL
     *
     * @param createClass 生成表创建sql
     * @return sql字符串
     * @throws Exception
     */
    public String getCreateTableSQL(Class createClass) throws Exception {
        TableModels soberTable = getSoberTable(createClass);
        Map<String, Object> valueMap = new HashMap<String, Object>();
        valueMap.put(Dialect.KEY_TABLE_NAME, soberTable.getTableName());

        /////////先创建每一个字段
        String[] columns = null;
        for (SoberColumn soberColumn : soberTable.getColumns()) {
            valueMap.put(Dialect.KEY_TABLE_NAME, soberTable.getTableName());
            if (soberColumn.getName().equalsIgnoreCase(soberTable.getPrimary())) {
                valueMap.put(Dialect.KEY_FIELD_SERIAL, soberTable.isSerial());
            } else {
                valueMap.put(Dialect.KEY_FIELD_SERIAL, false);
            }
            valueMap.put(Dialect.KEY_PRIMARY_KEY, soberTable.getPrimary());
            valueMap.put(Dialect.COLUMN_NAME, soberColumn.getName());
            valueMap.put(Dialect.COLUMN_LENGTH, soberColumn.getLength());
            valueMap.put(Dialect.COLUMN_NOT_NULL, soberColumn.isNotNull());
            valueMap.put(Dialect.COLUMN_DEFAULT, soberColumn.getDefaultValue());
            valueMap.put(Dialect.COLUMN_CAPTION, soberColumn.getCaption());
            columns = ArrayUtil.add(columns, dialect.processTemplate(soberColumn.getClassType().getName(), valueMap));
            valueMap.clear();
        }
        ///修补建表注释主要是pgsql   begin
        StringBuilder commentPatchSql = new StringBuilder();
        if (dialect.commentPatch()) {

            for (SoberColumn soberColumn : soberTable.getColumns()) {
                valueMap.put(Dialect.KEY_TABLE_NAME, soberTable.getTableName());
                valueMap.put(Dialect.COLUMN_NAME, soberColumn.getName());
                valueMap.put(Dialect.COLUMN_CAPTION, soberColumn.getCaption());
                commentPatchSql.append(dialect.processTemplate(Dialect.SQL_COMMENT, valueMap)).append(StringUtil.SEMICOLON).append(StringUtil.CRLF);
                valueMap.clear();
            }

        }
        ///修补建表注释主要是pgsql   end

        /////////在总体的生成SQL begin
        valueMap.put(Dialect.KEY_TABLE_NAME, soberTable.getTableName());
        valueMap.put(Dialect.KEY_COLUMN_LIST, columns);
        valueMap.put(Dialect.KEY_PRIMARY_KEY, soberTable.getPrimary());
        valueMap.put(Dialect.KEY_FIELD_SERIAL, soberTable.isSerial());
        /////////在总体的生成SQL end

        if (dialect.commentPatch() && !StringUtil.isNULL(commentPatchSql.toString()))
            return dialect.processTemplate(Dialect.SQL_CREATE_TABLE, valueMap) + StringUtil.SEMICOLON + StringUtil.CRLF + commentPatchSql.toString();
        return dialect.processTemplate(Dialect.SQL_CREATE_TABLE, valueMap);
    }

    /**
     * 删除表
     *
     * @param cla 删除表
     * @return 是否成功
     * @throws Exception
     */
    public boolean dropTable(Class cla) throws Exception {
        if (cla == null) return false;
        TableModels soberTable = getSoberTable(cla);
        Map<String, Object> valueMap = new HashMap<String, Object>();
        valueMap.put(Dialect.COLUMN_NAME, soberTable.getPrimary());
        valueMap.put(Dialect.KEY_TABLE_NAME, soberTable.getTableName());
        if (soberFactory.isUseCache() && soberTable.isUseCache()) {
            String cacheName = StringUtil.defaultIfEmpty(soberTable.getCacheName(), soberFactory.getCacheName());
            JSCacheManager.queryRemove(cacheName, soberTable.getEntity().getName() + ".*");
        }
        return execute(dialect.processTemplate(Dialect.SQL_DROP_TABLE, valueMap), null);
    }

    /**
     * 表是否存在
     *
     * @param cla bean对象是否存在表
     * @return 返回是否存在
     */
    public boolean tableExists(Class cla) {
        TableModels soberTable = getSoberTable(cla);
        Map<String, Object> valueMap = new HashMap<String, Object>();
        valueMap.put(Dialect.KEY_TABLE_NAME, soberTable.getTableName());
        valueMap.put(Dialect.COLUMN_NAME, soberTable.getPrimary());
        try {
            Object o = getUniqueResult(dialect.processTemplate(Dialect.FUN_TABLE_EXISTS, valueMap));
            return o instanceof String && soberTable.getTableName().equalsIgnoreCase((String) o) || getBoolean(o);
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    /**
     * @param cla 得到最大ID
     * @return ID数
     */
    public long getTableMaxId(Class cla) {
        TableModels soberTable = getSoberTable(cla);
        Map<String, Object> valueMap = new HashMap<String, Object>();
        valueMap.put(Dialect.KEY_TABLE_NAME, soberTable.getTableName());
        valueMap.put(Dialect.KEY_PRIMARY_KEY, soberTable.getPrimary());
        try {
            return ObjectUtil.toLong(getUniqueResult(dialect.processTemplate(Dialect.TABLE_MAX_ID, valueMap)));
        } catch (Exception e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * @param databaseName 数据库名称
     * @return 得到数据库大小
     */
    public long getDataBaseSize(String databaseName) {
        Map<String, Object> valueMap = new HashMap<String, Object>();
        valueMap.put(Dialect.KEY_DATABASE_NAME, databaseName);
        try {
            return ObjectUtil.toLong(getUniqueResult(dialect.processTemplate(Dialect.DATABASE_SIZE, valueMap)));
        } catch (Exception e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * @return 返回数组
     */
    public String[] getTableNames() {
        Connection conn = null;
        Statement statement = null;
        ResultSet resultSet = null;
        String[] result = null;
        //取出cache  begin

        String sqlText = StringUtil.empty;
        try {
            sqlText = dialect.processTemplate(Dialect.SQL_TABLE_NAMES, new HashMap<String, Object>());
            debugPrint(sqlText);
            conn = getConnection(SoberEnv.ReadOnly);

            if (!dialect.supportsConcurReadOnly())
                statement = conn.createStatement();
            else
                statement = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);

            resultSet = statement.executeQuery(sqlText);
            while (resultSet.next()) {
                result = ArrayUtil.add(result, (String) dialect.getResultSetValue(resultSet, 1));
            }
        } catch (Exception e) {
            log.error("SQL:" + sqlText, e);
            soberFactory.closeConnection(conn, true);
        } finally {
            JdbcUtil.closeResultSet(resultSet);
            JdbcUtil.closeStatement(statement);
            closeConnection(conn);
        }
        return result;
    }


    /**
     * @param cla 类对象
     * @return 得到数据库序列名称
     */
    public String getSequenceName(Class cla) {
        if (!dialect.supportsSequenceName()) return null;
        TableModels soberTable = getSoberTable(cla);
        Map<String, Object> valueMap = new HashMap<String, Object>();
        valueMap.put(Dialect.KEY_TABLE_NAME, soberTable.getTableName());
        valueMap.put(Dialect.KEY_PRIMARY_KEY, soberTable.getPrimary());
        try {
            Object o = getUniqueResult(dialect.processTemplate(Dialect.SEQUENCE_NAME, valueMap));
            return StringUtil.substringBetween((String) o, "'", "'");
        } catch (Exception e) {
            e.printStackTrace();
            return StringUtil.empty;
        }
    }

    /**
     * @param cla   类对象
     * @param start 序列值
     * @return 设置序列开始值
     */
    public boolean alterSequenceStart(Class cla, long start) {
        if (start <= 0) return false;
        TableModels soberTable = getSoberTable(cla);
        Map<String, Object> valueMap = new HashMap<String, Object>();
        valueMap.put(Dialect.KEY_TABLE_NAME, soberTable.getTableName());
        valueMap.put(Dialect.KEY_PRIMARY_KEY, soberTable.getPrimary());
        valueMap.put(Dialect.SERIAL_NAME, getSequenceName(cla));
        valueMap.put(Dialect.KEY_SEQUENCE_RESTART, start);
        try {
            return execute(dialect.processTemplate(Dialect.ALTER_SEQUENCE_RESTART, valueMap), null);
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }


    private boolean getBoolean(Object o) {
        if (o == null) {
            return false;
        }
        if (o instanceof Boolean) {
            return (Boolean) o;
        } else if (o instanceof Integer) {
            Integer in = (Integer) o;
            return in > 0;
        } else if (o instanceof Long) {
            Long in = (Long) o;
            return in > 0;
        } else if (o instanceof BigDecimal) {
            BigDecimal b = (BigDecimal) o;
            return b.intValue() > 0;
        } else if (o instanceof List) {
            List list = (List) o;
            return !list.isEmpty();
        } else if (o instanceof Map) {
            Map map = (Map) o;
            return !map.isEmpty();
        } else if (o instanceof String && StringUtil.isNULL((String) o)) {
            return false;
        }
        return ObjectUtil.toBoolean(o);
    }
    //------------------------------------------------------------------------------------------------------------------

    /**
     * @param sqlText sql
     * @param parm    参数
     * @return 执行一个存储过程
     */
    public boolean prepareExecute(String sqlText, Object parm[]) {
        Connection conn = null;
        CallableStatement statement = null;
        try {
            conn = getConnection(SoberEnv.ReadWrite);
            statement = conn.prepareCall(sqlText);
            debugPrint(sqlText);
            for (int i = 0; i < parm.length; i++) {
                debugPrint("prepared[" + (i + 1) + "]=" + parm[i]);
                dialect.setPreparedStatementValue(statement, i + 1, parm[i]);
            }
            return statement.execute();
        } catch (Exception e) {
            log.error("ERROR SQL:" + sqlText, e);
            e.printStackTrace();
            soberFactory.closeConnection(conn, true);
            return false;
        } finally {
            JdbcUtil.closeStatement(statement);
            closeConnection(conn);
        }
    }

    /**
     * 更新一个存储过程
     *
     * @param sqlText sql
     * @param parm    参数
     * @return update 返回， jdbc
     */
    public int prepareUpdate(String sqlText, Object parm[]) {
        Connection conn = null;
        CallableStatement statement = null;
        try {
            conn = getConnection(SoberEnv.ReadWrite);
            statement = conn.prepareCall(sqlText);
            for (int i = 0; i < parm.length; i++) {
                debugPrint("prepared[" + (i + 1) + "]=" + parm[i]);
                dialect.setPreparedStatementValue(statement, i + 1, parm[i]);
            }
            return statement.executeUpdate();
        } catch (Exception e) {
            log.error("ERROR SQL:" + sqlText, e);
            soberFactory.closeConnection(conn, true);
            return -2;
        } finally {
            JdbcUtil.closeStatement(statement);
            closeConnection(conn);
        }
    }

    /**
     * @param sqlText sql
     * @param param   参数
     * @return 存储过程调用
     */
    @SuppressWarnings("unchecked")
    public List<DataMap> prepareQuery(String sqlText, Object param[]) {
        List<DataMap> result = null;
        Connection conn = null;
        CallableStatement statement = null;
        ResultSet resultSet = null;
        try {
            conn = getConnection(SoberEnv.ReadOnly);
            statement = conn.prepareCall(sqlText);
            if (!ArrayUtil.isEmpty(param))
                for (int i = 0; i < param.length; i++) {
                    debugPrint("prepared[" + (i + 1) + "]=" + param[i]);
                    dialect.setPreparedStatementValue(statement, i + 1, param[i]);
                }
            resultSet = statement.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            result = new ArrayList<DataMap>();
            while (resultSet.next()) {
                DataMap<String, Object> valueMap = new DataMap<String, Object>();
                for (int n = 1; n <= metaData.getColumnCount(); n++) {
                    valueMap.put(metaData.getColumnLabel(n), dialect.getResultSetValue(resultSet, n));
                }
                result.add(valueMap);
            }
        } catch (Exception e) {
            log.error("ERROR SQL:" + sqlText, e);
            e.printStackTrace();
            soberFactory.closeConnection(conn, true);
            return result;
        } finally {
            JdbcUtil.closeResultSet(resultSet);
            JdbcUtil.closeStatement(statement);
            closeConnection(conn);
        }

        return result;
    }

    //------------------------------------------------------------------------------------------------------------------

    /**
     * 验证bean
     *
     * @param obj 验证的bean
     * @throws Exception      其他错误
     * @throws ValidException 验证错误
     */
    public void validator(Object obj) throws Exception {
        Map<String, String> result = new HashMap<String, String>();
        ScriptRunner scriptRunner = new TemplateScriptEngine();
        TableModels soberTable = getSoberTable(obj.getClass());
        try {
            for (SoberColumn soberColumn : soberTable.getColumns()) {
                if (!soberColumn.isNotNull()) continue;
                String dataType = soberColumn.getDataType();
                if (StringUtil.isNULL(dataType)) continue;
                Object value = BeanUtil.getProperty(obj, soberColumn.getName());
                if (value instanceof InputStream) continue;
                boolean isNeed = soberColumn.isNotNull();
                if (value != null) {
                    isNeed = true;
                }
                if (!isNeed) continue;
                String expression = dataType;
                if (expression.startsWith(" ")) {
                    expression = expression.trim();
                } else {
                    expression = soberColumn.getName() + "." + expression;
                }
                if (!expression.endsWith(")")) expression = expression + "()";
                if (value == null) value = StringUtil.empty;
                scriptRunner.put(soberColumn.getName(), value);
                if (!ObjectUtil.toBoolean(scriptRunner.eval(expression,0))) {
                    result.put(soberColumn.getName(), "value:" + value + " dataType:" + dataType);
                }
                scriptRunner.put(soberColumn.getName(), null);
            }
            if (!result.isEmpty()) {
                throw new ValidException(result, obj);
            }
        } finally {
            scriptRunner.exit();
        }
    }

    /**
     * 简单表达式查询
     * SSqlExpression
     *
     * @param aClass      类
     * @param term        条件
     * @param orderBy     排序
     * @param currentPage 页
     * @param totalCount  一页多少记录
     * @param loadChild   载入映射否
     * @return 执行结果
     * @throws Exception
     */
    public List getExpressionList(Class aClass, String term, String orderBy, int currentPage, int totalCount, boolean loadChild) throws Exception {
        if (totalCount > getMaxRows()) totalCount = getMaxRows();
        Criteria criteria = createCriteria(aClass);
        if (!StringUtil.isNULL(term)) {
            criteria = SSqlExpression.getTermExpression(criteria, term);
        }
        if (!StringUtil.isNULL(orderBy)) {
            criteria = SSqlExpression.getSortOrder(criteria, orderBy);
        }
        criteria = criteria.setCurrentPage(currentPage).setTotalCount(totalCount);
        return criteria.list(loadChild);
    }

    /**
     * 用来计算总数很方便
     * SSqlExpression
     * 简单表达式查询得到行数
     *
     * @param aClass 类
     * @param term   条件
     * @return 得到单一的返回
     * @throws Exception
     */
    public int getExpressionCount(Class aClass, String term) throws Exception {
        Criteria criteria = createCriteria(aClass);
        if (!StringUtil.isNULL(term)) {
            criteria = SSqlExpression.getTermExpression(criteria, term);
        }
        return criteria.setProjection(Projections.rowCount()).intUniqueResult();
    }

    /**
     * 创建标准查询
     *
     * @param cla 类对象
     * @return Criteria 查询器
     */
    public Criteria createCriteria(Class cla) {
        return new CriteriaImpl(cla, this);
    }

    /**
     * sql map 查询器
     *
     * @return SqlMapClient
     */
    public SqlMapClient buildSqlMap() {
        return new SqlMapClientImpl(this);
    }

    /**
     * @param info 控制台输出SQL
     */
    public void debugPrint(String info) {
        if (soberFactory.isShowsql()) {
            System.out.println(info);
        }
    }

    /**
     * 清除缓存
     *
     * @param cla 类
     */
    public void evict(Class cla) {
        TableModels soberTable = getSoberTable(cla);
        String cacheName;
        if (soberFactory.isUseCache() && soberTable.isUseCache()) {
            cacheName = StringUtil.defaultIfEmpty(soberTable.getCacheName(), soberFactory.getCacheName());
            JSCacheManager.queryRemove(cacheName, cla.getName() + ".*");
        }
    }
    /**
     *  清除缓存 中list 相关数据
     * @param cla
     */
    public void evictList(Class cla)
    {
        TableModels soberTable = getSoberTable(cla);
        String cacheName;
        if (soberFactory.isUseCache() && soberTable.isUseCache()) {
            cacheName = StringUtil.defaultIfEmpty(soberTable.getCacheName(), soberFactory.getCacheName());
            JSCacheManager.queryRemove(cacheName, cla.getName() + SoberUtil.cache_trem_list + ".*");
        }
    }

    /**
     * 清除缓存 中load 相关数据
     * @param cla
     */
    public void evictLoad(Class cla) {
        TableModels soberTable = getSoberTable(cla);
        String cacheName;
        if (soberFactory.isUseCache() && soberTable.isUseCache()) {
            cacheName = StringUtil.defaultIfEmpty(soberTable.getCacheName(), soberFactory.getCacheName());
            JSCacheManager.queryRemove(cacheName, cla.getName() + SoberUtil.cache_trem_load + ".*");
        }
    }

}
